package com.engine.core.gen;


import com.engine.core.domain.GenTable;
import com.engine.core.domain.GenTableColumn;
import com.engine.core.rowMapper.MappedType;
import com.engine.core.util.Utils;
import com.engine.dialect.DialectType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Optional;

public class Mysql implements  DialectType {

    private String dbname;
    public Mysql(){
        this.dbname="mysql";
    }

    /** 数据库字符串类型 */
    public static final String[] COLUMNTYPE_STR = { "char", "varchar", "narchar", "varchar2", "tinytext", "text",
            "mediumtext", "longtext" };

    /** 数据库时间类型 */
    public static final String[] COLUMNTYPE_TIME = { "datetime", "time", "date" };

    public static final String[] COLUMNTYPE_TIMESTAMP = {  "timestamp" };


    /** 数据库数字类型 */
    public static final String[] COLUMNTYPE_NUMBER = { "tinyint", "smallint", "mediumint", "int", "number", "integer",
            "bigint", "float", "float", "double", "decimal" };

    public static final String[] COLUMNTYPE_NUMBER_INT = { "tinyint", "smallint", "mediumint", "int"};

    public static final String[] COLUMNTYPE_NUMBER_DOUBLE = { "float", "float", "double", "decimal"};

    public static final String[] COLUMNTYPE_NUMBER_LONG={"bigint"};



    //查列名
    final static  String sqlTabelColumn= "select column_name, (case when (is_nullable = 'no' and column_key != 'PRI') then '1' else null end) as is_required, (case when column_key = 'PRI' then '1' else '0' end) as is_pk, ordinal_position as sort, column_comment, (case when extra = 'auto_increment' then '1' else '0' end) as is_increment, column_type "+
                            " from information_schema.columns where table_schema = (select database()) and table_name = ? order by ordinal_position ";
    //查表名
    final static String sqlTabel= "select table_name, table_comment, create_time, update_time from information_schema.tables where  table_schema = (select database()) and table_name = ?";

    /**
     * 获取数据库类型字段
     * @param columnType 列类型
     * @return 截取后的列类型
     */
    public static String getDbType(String columnType)
    {
        if (columnType.indexOf("(") > 0)
        {
            return columnType.substring(0,columnType.indexOf("("));
        }
        else
        {
            return columnType;
        }
    }

    public static Object javaValueByColumnType(String columnType,String value){
        try {
            if(Arrays.asList(COLUMNTYPE_NUMBER_INT).contains(columnType)){
                  return Utils.convert(value,"Integer");
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_LONG).contains(columnType)){
                return Utils.convert(value,"Long");
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_DOUBLE).contains(columnType)){
                return Utils.convert(value,"Double");
            }
        } catch (Exception e) {
        }
        return value;
    }

    public static Object javaValueByColumnTypeFormat(String columnType,String value,String format[]){
        try {
            if(Arrays.asList(COLUMNTYPE_NUMBER_INT).contains(columnType)){
                return Utils.convert(value,"Integer",format);
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_LONG).contains(columnType)){
                return Utils.convert(value,"Long",format);
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_DOUBLE).contains(columnType)){
                return Utils.convert(value,"Double",format);
            }else if(Arrays.asList(COLUMNTYPE_TIME).contains(columnType)){
                return Utils.convert(value,"Date",format);
            }else if(Arrays.asList(COLUMNTYPE_STR).contains(columnType)){
                return Utils.convert(value,"String",format);
            }
        } catch (Exception e) {
        }
        return value;
    }

    public static String javaTypeByColumnType(String columnType){
        try {
            if(Arrays.asList(COLUMNTYPE_NUMBER_INT).contains(getDbType(columnType))){
                return "Integer";
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_LONG).contains(getDbType(columnType))){
                return "Long";
            }else if(Arrays.asList(COLUMNTYPE_NUMBER_DOUBLE).contains(getDbType(columnType))){
                return "Double";
            }else if(Arrays.asList(COLUMNTYPE_TIME).contains(getDbType(columnType))){
                return "Date";
            }else if(Arrays.asList(COLUMNTYPE_STR).contains(getDbType(columnType))){
                return "String";
            }else if(Arrays.asList(COLUMNTYPE_TIMESTAMP).contains(getDbType(columnType))){
                return "Timestamp";
            }
        } catch (Exception e) {
        }
        return "String";
    }

    public static String getPageSql(String sql,PagedOption option){
        StringBuilder sqlBuilder=new StringBuilder(sql);
        if(option.getParamMode()==PagedOption.PARAM_MODE_ARRAY){
            sqlBuilder.append(" limit ?,? ");
        }else{
            sqlBuilder.append(" limit :offset,:limit ");
        }
        return sqlBuilder.toString();
    }

    public static String getPageCountSql(String sql,PagedOption option){
        StringBuilder sqlBuilder=new StringBuilder("select count(*) cn from ( ");
        sqlBuilder.append(sql);
        sqlBuilder.append(" ) tb");
        return sqlBuilder.toString();
    }

    public static GenTable getTable(String tableName, JdbcTemplate jdbcTemplate){
         GenTable genTable= jdbcTemplate.queryForObject(sqlTabel, new RowMapper<GenTable>() {
            @Override
            public GenTable mapRow(ResultSet rs, int i) throws SQLException {
                GenTable genTable=new GenTable();
                genTable.setResourceName(rs.getString(1));
                genTable.setResourceRemark(rs.getString(2));
                return genTable;
            }
         },new Object[]{tableName});
       List<GenTableColumn>  columnList=jdbcTemplate.query(sqlTabelColumn,new Object[]{tableName},new RowMapper<GenTableColumn>(){
                /**
                 * @param rs
                 * @param i
                 * @return
                 * @throws SQLException
                 */
                @Override
                public GenTableColumn mapRow(ResultSet rs, int i) throws SQLException {
                    GenTableColumn genTableColumn=new GenTableColumn();
                    genTableColumn.setColumnName(rs.getString(1));
                    genTableColumn.setIsRequired(rs.getString(2));
                    genTableColumn.setIsPk(rs.getString(3));
                    genTableColumn.setIsIncrement(rs.getString("is_increment"));
                    genTableColumn.setColumnComment(rs.getString("column_comment"));
                    genTableColumn.setColumnType(rs.getString("column_type"));
                    return genTableColumn;
                }
        });
        Optional<GenTableColumn> pkOptional= columnList.stream().filter(s->s.isPk()).findFirst();
        if(pkOptional.isPresent()){
            genTable.setPkColumn(pkOptional.get());
        }
        genTable.setColumns(columnList);
         return genTable;
    }

    /**
     * 根据值类型获取sqlType
     * @param o
     * @param targetSqlType
     * @return
     */
    public static int getUpdateSqlType(Object o, int targetSqlType, MappedType dbTypeName) {
         return targetSqlType;
    }


    @Override
    public  boolean isSupport(String dbType) {
        return dbType.equals(this.dbname);
    }


    /**
     * like查询表达式
     * @param column 列名
     * @param paramAlias 参数名
     * @param funStr
     * @param isNot
     * @return
     */
    @Override
    public String like(String column, String paramAlias, String funStr, boolean isNot) {
        if(funStr==null || funStr.length()==0){
            if(!isNot){
                return column +" like concat('%',:"+paramAlias+",'%')";
            }else{
                return column +" not like concat('%',:"+paramAlias+",'%')";
            }
        }else{
            if(!isNot){
                return column +" like concat('%',"+funStr+",'%')";
            }else{
                return column +" not like concat('%',"+funStr+",'%')";
            }
        }
    }

    @Override
    public String leftLike(String column, String paramAlias, String funStr, boolean isNot) {
        if(funStr==null || funStr.length()==0){
            if(!isNot){
                return column +" like concat(:"+paramAlias+",'%')";
            }else{
                return column +" not like concat(:"+paramAlias+",'%')";
            }
        }else{
            if(!isNot){
                return column +" like concat("+funStr+",'%')";
            }else{
                return column +" not like concat("+funStr+",'%')";
            }
        }
    }

    @Override
    public String rightLike(String column, String paramAlias, String funStr, boolean isNot) {
        if(funStr==null || funStr.length()==0){
            if(!isNot){
                return column +" like concat('%',:"+paramAlias+")";
            }else{
                return column +" not like concat('%',:"+paramAlias+")";
            }
        }else{
            if(!isNot){
                return column +" like concat('%',"+funStr+")";
            }else{
                return column +" not like concat('%',"+funStr+")";
            }
        }
    }

    @Override
    public String contain(String column, String paramAlias, String funStr, boolean isNot) {
        if(funStr==null || funStr.length()==0){
            if(!isNot){
                return "INSTR(CONCAT(',',"+column+",','),CONCAT(',',:"+paramAlias+",',')) >0 ";
            }else{
                return "INSTR(CONCAT(',',"+column+",','),CONCAT(',',:"+paramAlias+",',')) =0 ";
            }
        }else{
            if(!isNot){
                return "INSTR(CONCAT(',',"+column+",','),CONCAT(',',"+funStr+",',')) >0 ";
            }else{
                return "INSTR(CONCAT(',',"+column+",','),CONCAT(',',"+funStr+",',')) =0 ";
            }
        }
    }

    @Override
    public String containRight(String column, String paramAlias, String funStr, boolean isNot) {
        if(funStr==null || funStr.length()==0){
            if(!isNot){
                return "INSTR(CONCAT(',',:"+paramAlias+",','),CONCAT(',',"+column+",',')) >0 ";
            }else{
                return "INSTR(CONCAT(',',:"+paramAlias+",','),CONCAT(',',"+column+",',')) =0 ";
            }
        }else{
            if(!isNot){
                return "INSTR(CONCAT(',',"+funStr+",','),CONCAT(',',"+column+",',')) >0 ";
            }else{
                return "INSTR(CONCAT(',',"+funStr+",','),CONCAT(',',"+column+",',')) =0 ";
            }
        }
    }

}
